{smcl}
{com}{sf}{ul off}{txt}{.-}
      name:  {res}<unnamed>
       {txt}log:  {res}H:\Dropbox\work\replicationpack\logs\2_PrepSample.smcl
  {txt}log type:  {res}smcl
 {txt}opened on:  {res}29 Nov 2024, 20:59:47
{txt}
{com}. 
. ************************************************************************************************************
. /************************** Join Data to Create Analysis Sample ************************************/
. ************************************************************************************************************
. 
. *--------------------------------------------------
. * Get criminal case data
. *--------------------------------------------------
. 
. * Load courts data
. cd "$workpath"
{res}H:\Dropbox\work\replicationpack
{txt}
{com}. use data\mdcourtsdata.dta, clear
{txt}
{com}. 
. * Rename
. rename DefendantName1_ defname
{res}{txt}
{com}. 
. * Drop irrelevant variables
. keep casenumber ZipCode1_ DOB1_ Sex Race Court defname date_init *_init cjisclass charge cjiscode ///
> felony jailable phrqd maxfine timetype timeamt charge_* init_* defendant* male ln_age disposition_* ///
> bailreview balr_* final_* bond_* normalbond upfrontbond wari_* timedetained ///
> timelyrelease* numcases* court* iv* riv* chargeyr zipyr sexrace ln_*
{txt}
{com}. 
. * Drop pre-2000 observations
. drop if year_init < 2000
{txt}(1 observation deleted)

{com}. 
. * Detention is defined as 1 - Released
. gen detained = 1-init_statusROR
{txt}
{com}. 
. *--------------------------------------------------
. * Define variables
. *--------------------------------------------------
. 
. * Dummy variables
. gen byte black = Race == 3
{txt}
{com}. gen byte young = defendant_age < 30
{txt}
{com}. 
. * Get apartment flag
. merge 1:1 casenumber using data\crimcases_address.dta, update keep(1 3) assert(1 2 3) keepusing(isapt)
{res}
{txt}{col 5}Result{col 38}# of obs.
{col 5}{hline 41}
{col 5}not matched{col 30}{res}         140,805
{txt}{col 9}from master{col 30}{res}         140,805{txt}  (_merge==1)
{col 9}from using{col 30}{res}               0{txt}  (_merge==2)

{col 5}matched{col 30}{res}       1,063,819
{txt}{col 9}not updated{col 30}{res}       1,063,819{txt}  (_merge==3)
{col 9}missing updated{col 30}{res}               0{txt}  (_merge==4)
{col 9}nonmissing conflict{col 30}{res}               0{txt}  (_merge==5)
{col 5}{hline 41}

{com}. drop _merge
{txt}
{com}. 
. *--------------------------------------------------
. * Get Zillow data
. *--------------------------------------------------
. 
. * Get Zillow transactions 
. merge m:1 casenumber using data\cases_zillowlinked.dta, update assert(1 2 3) keep(1 3)
{res}
{txt}{col 5}Result{col 38}# of obs.
{col 5}{hline 41}
{col 5}not matched{col 30}{res}         712,841
{txt}{col 9}from master{col 30}{res}         712,841{txt}  (_merge==1)
{col 9}from using{col 30}{res}               0{txt}  (_merge==2)

{col 5}matched{col 30}{res}         491,783
{txt}{col 9}not updated{col 30}{res}         491,783{txt}  (_merge==3)
{col 9}missing updated{col 30}{res}               0{txt}  (_merge==4)
{col 9}nonmissing conflict{col 30}{res}               0{txt}  (_merge==5)
{col 5}{hline 41}

{com}. rename _merge _m_zillow
{res}{txt}
{com}. 
. * Flag post sale/foreclosure only if it's the first transaction
. foreach type in 3mos 6mos 1yr 2yr 3yr {c -(} 
{txt}  2{com}.         replace sellhouse_`type' = 0 if postselldate > posttsctdate & !missing(posttsctdate)    
{txt}  3{com}.         replace foreclose_`type' = 0 if postfcldate > posttsctdate & !missing(posttsctdate)
{txt}  4{com}. {c )-}
{txt}(33 real changes made)
(11 real changes made)
(234 real changes made)
(44 real changes made)
(1,498 real changes made)
(217 real changes made)
(5,572 real changes made)
(1,233 real changes made)
(11,175 real changes made)
(3,202 real changes made)

{com}. 
. * Flag pre sale/foreclosure only if it's the last transaction
. foreach type in 3mos 6mos 1yr 2yr 3yr {c -(} 
{txt}  2{com}.         replace sellhouse_`type'pre = 0 if preselldate < pretsctdate & !missing(pretsctdate)    
{txt}  3{com}.         replace foreclose_`type'pre = 0 if prefcldate < pretsctdate & !missing(pretsctdate)
{txt}  4{com}. {c )-}
{txt}(18 real changes made)
(30 real changes made)
(64 real changes made)
(194 real changes made)
(231 real changes made)
(796 real changes made)
(860 real changes made)
(2,739 real changes made)
(1,957 real changes made)
(5,606 real changes made)

{com}. 
. * Recode transaction date as missing if not first transaction after hearing date
. replace postselldate = . if postselldate > posttsctdate & !missing(posttsctdate)
{txt}(42,969 real changes made, 42,969 to missing)

{com}. replace postfcldate = . if postfcldate > posttsctdate & !missing(posttsctdate)
{txt}(51,266 real changes made, 51,266 to missing)

{com}. 
. * Recode transaction date as missing if not last transaction after hearing date
. replace preselldate = . if preselldate < posttsctdate & !missing(posttsctdate)
{txt}(230,446 real changes made, 230,446 to missing)

{com}. replace prefcldate = . if prefcldate < posttsctdate & !missing(posttsctdate)
{txt}(17,802 real changes made, 17,802 to missing)

{com}. 
. * Zero out missing
. foreach var of varlist sellhouse_* foreclose_* {c -(}
{txt}  2{com}.         rename `var' z`var'
{txt}  3{com}.         quietly replace z`var' = 0 if missing(z`var')   
{txt}  4{com}. {c )-}
{res}{txt}
{com}. 
. * Name-matched version of variables
. foreach var of varlist zforeclose_* {c -(}
{txt}  2{com}.         gen `var'_nml = `var' & prebuyer_nml == 1
{txt}  3{com}.         gen `var'_nmf = `var' & prebuyer_nmf == 1
{txt}  4{com}.         gen `var'_fml = `var' & prebuyer_fml == 1
{txt}  5{com}.         gen `var'_fmf = `var' & prebuyer_fmf == 1
{txt}  6{com}.         gen `var'_dml = `var' & prebuyer_dml == 1
{txt}  7{com}.         gen `var'_dmf = `var' & prebuyer_dmf == 1
{txt}  8{com}. {c )-} 
{txt}
{com}. rename zforeclose*_nml zforeclose_nml*
{res}{txt}
{com}. rename zforeclose*_nmf zforeclose_nmf*
{res}{txt}
{com}. rename zforeclose*_fml zforeclose_fml*
{res}{txt}
{com}. rename zforeclose*_fmf zforeclose_fmf*
{res}{txt}
{com}. rename zforeclose*_dml zforeclose_dml*
{res}{txt}
{com}. rename zforeclose*_dmf zforeclose_dmf*
{res}{txt}
{com}. 
. * Age at purchase
. gen dob = date(DOB1_,"MDY")
{txt}(2,956 missing values generated)

{com}. format dob %td
{txt}
{com}. gen date_purchase = date_init + pretsctdate
{txt}(828,606 missing values generated)

{com}. format date_purchase %td
{txt}
{com}. gen age_purchase = (date_purchase - dob)/365
{txt}(829,350 missing values generated)

{com}. 
. * Housing characteristic indicators (above/below sample median)
. local filters "inrange(year_init,2000,2016) & !isapt & !missing(pretsctdate) & numcases_percommyr>50"
{txt}
{com}. foreach var of varlist pretsctdate preprice preloantoprice preloanamt prematurity age_purchase {c -(}
{txt}  2{com}.         egen temp = median(`var') if `filters'
{txt}  3{com}.         gen `var'_high = `var'>=temp if !missing(`var') & !missing(temp)
{txt}  4{com}.         drop temp
{txt}  5{com}. {c )-}
{txt}(919,915 missing values generated)
(919,915 missing values generated)
(919,915 missing values generated)
(1,062,999 missing values generated)
(919,915 missing values generated)
(1,066,423 missing values generated)
(919,915 missing values generated)
(938,227 missing values generated)
(919,915 missing values generated)
(1,108,592 missing values generated)
(919,915 missing values generated)
(920,446 missing values generated)

{com}. 
. *--------------------------------------------------
. * Get bankruptcy data
. *--------------------------------------------------
. 
. * Get bankruptcies 
. merge m:1 casenumber using data\cases_gnwlinked.dta, update assert(1 2 3) keep(1 3) keepusing(bankrupt*_* isapt_gnw postbankrupt* prebankrupt*)
{res}
{txt}{col 5}Result{col 38}# of obs.
{col 5}{hline 41}
{col 5}not matched{col 30}{res}       1,032,698
{txt}{col 9}from master{col 30}{res}       1,032,698{txt}  (_merge==1)
{col 9}from using{col 30}{res}               0{txt}  (_merge==2)

{col 5}matched{col 30}{res}         171,926
{txt}{col 9}not updated{col 30}{res}         171,926{txt}  (_merge==3)
{col 9}missing updated{col 30}{res}               0{txt}  (_merge==4)
{col 9}nonmissing conflict{col 30}{res}               0{txt}  (_merge==5)
{col 5}{hline 41}

{com}. rename _merge _m_gnw
{res}{txt}
{com}. 
. * Zero out missing
. foreach var of varlist bankrupt*_* {c -(}
{txt}  2{com}.         rename `var' z`var'
{txt}  3{com}.         quietly replace z`var' = 0 if missing(z`var')   
{txt}  4{com}. {c )-}
{res}{txt}
{com}. replace isapt_gnw = 0 if missing(isapt_gnw)
{txt}(1,032,698 real changes made)

{com}. 
. * Zero out post-hearing bankruptcies if occuring after Zillow transaction
. foreach var of varlist zbankrupt*_*mos zbankrupt*_*yr {c -(}
{txt}  2{com}.         replace `var' = 0 if postbankruptdate > posttsctdate & !missing(posttsctdate)
{txt}  3{com}. {c )-}
{txt}(88 real changes made)
(51 real changes made)
(36 real changes made)
(51 real changes made)
(30 real changes made)
(31 real changes made)
(14 real changes made)
(221 real changes made)
(119 real changes made)
(88 real changes made)
(117 real changes made)
(73 real changes made)
(84 real changes made)
(35 real changes made)
(28 real changes made)
(15 real changes made)
(8 real changes made)
(15 real changes made)
(7 real changes made)
(7 real changes made)
(3 real changes made)
(75 real changes made)
(35 real changes made)
(24 real changes made)
(35 real changes made)
(19 real changes made)
(25 real changes made)
(11 real changes made)
(61 real changes made)
(36 real changes made)
(28 real changes made)
(36 real changes made)
(23 real changes made)
(24 real changes made)
(11 real changes made)
(145 real changes made)
(84 real changes made)
(64 real changes made)
(82 real changes made)
(54 real changes made)
(59 real changes made)
(24 real changes made)
(535 real changes made)
(287 real changes made)
(226 real changes made)
(279 real changes made)
(198 real changes made)
(206 real changes made)
(74 real changes made)
(1,456 real changes made)
(732 real changes made)
(585 real changes made)
(700 real changes made)
(516 real changes made)
(520 real changes made)
(194 real changes made)
(2,665 real changes made)
(1,243 real changes made)
(966 real changes made)
(1,182 real changes made)
(855 real changes made)
(878 real changes made)
(293 real changes made)
(240 real changes made)
(117 real changes made)
(92 real changes made)
(116 real changes made)
(79 real changes made)
(86 real changes made)
(35 real changes made)
(779 real changes made)
(342 real changes made)
(268 real changes made)
(328 real changes made)
(231 real changes made)
(244 real changes made)
(96 real changes made)
(1,579 real changes made)
(670 real changes made)
(500 real changes made)
(632 real changes made)
(437 real changes made)
(467 real changes made)
(156 real changes made)
(297 real changes made)
(173 real changes made)
(135 real changes made)
(166 real changes made)
(120 real changes made)
(122 real changes made)
(40 real changes made)
(707 real changes made)
(400 real changes made)
(323 real changes made)
(379 real changes made)
(289 real changes made)
(284 real changes made)
(100 real changes made)
(1,166 real changes made)
(610 real changes made)
(489 real changes made)
(579 real changes made)
(438 real changes made)
(441 real changes made)
(146 real changes made)

{com}. 
. * Zero out pre-hearing bankruptcies if occuring before Zillow transaction
. foreach var of varlist zbankrupt*_*pre {c -(}
{txt}  2{com}.         replace `var' = 0 if prebankruptdate < pretsctdate & !missing(pretsctdate)
{txt}  3{com}. {c )-}
{txt}(19 real changes made)
(7 real changes made)
(5 real changes made)
(2 real changes made)
(2 real changes made)
(7 real changes made)
(3 real changes made)
(106 real changes made)
(35 real changes made)
(24 real changes made)
(16 real changes made)
(14 real changes made)
(30 real changes made)
(14 real changes made)
(399 real changes made)
(133 real changes made)
(73 real changes made)
(52 real changes made)
(37 real changes made)
(119 real changes made)
(48 real changes made)
(1,646 real changes made)
(524 real changes made)
(280 real changes made)
(198 real changes made)
(144 real changes made)
(469 real changes made)
(162 real changes made)
(3,831 real changes made)
(1,093 real changes made)
(558 real changes made)
(425 real changes made)
(332 real changes made)
(957 real changes made)
(289 real changes made)
(9 real changes made)
(3 real changes made)
(3 real changes made)
(0 real changes made)
(0 real changes made)
(3 real changes made)
(3 real changes made)
(52 real changes made)
(19 real changes made)
(13 real changes made)
(7 real changes made)
(7 real changes made)
(18 real changes made)
(9 real changes made)
(210 real changes made)
(64 real changes made)
(34 real changes made)
(16 real changes made)
(16 real changes made)
(60 real changes made)
(27 real changes made)
(941 real changes made)
(274 real changes made)
(139 real changes made)
(86 real changes made)
(66 real changes made)
(247 real changes made)
(87 real changes made)
(2,335 real changes made)
(621 real changes made)
(292 real changes made)
(214 real changes made)
(165 real changes made)
(548 real changes made)
(159 real changes made)
(10 real changes made)
(4 real changes made)
(2 real changes made)
(2 real changes made)
(2 real changes made)
(4 real changes made)
(0 real changes made)
(54 real changes made)
(16 real changes made)
(11 real changes made)
(9 real changes made)
(7 real changes made)
(12 real changes made)
(5 real changes made)
(190 real changes made)
(69 real changes made)
(39 real changes made)
(36 real changes made)
(21 real changes made)
(59 real changes made)
(21 real changes made)
(734 real changes made)
(259 real changes made)
(147 real changes made)
(114 real changes made)
(79 real changes made)
(231 real changes made)
(80 real changes made)
(1,604 real changes made)
(495 real changes made)
(279 real changes made)
(220 real changes made)
(172 real changes made)
(430 real changes made)
(138 real changes made)

{com}. 
. *--------------------------------------------------
. * Get civil court data
. *--------------------------------------------------
. 
. * Get civil cases 
. merge m:1 casenumber using data\cases_civlinked.dta, update assert(1 2 3) keep(1 3) keepusing(civfcl*_* lien*_* isapt_civ post*date pre*date)
{res}
{txt}{col 5}Result{col 38}# of obs.
{col 5}{hline 41}
{col 5}not matched{col 30}{res}         992,874
{txt}{col 9}from master{col 30}{res}         992,874{txt}  (_merge==1)
{col 9}from using{col 30}{res}               0{txt}  (_merge==2)

{col 5}matched{col 30}{res}         211,750
{txt}{col 9}not updated{col 30}{res}         211,750{txt}  (_merge==3)
{col 9}missing updated{col 30}{res}               0{txt}  (_merge==4)
{col 9}nonmissing conflict{col 30}{res}               0{txt}  (_merge==5)
{col 5}{hline 41}

{com}. rename _merge _m_civ
{res}{txt}
{com}. 
. * Zero out missing
. foreach var of varlist civfcl*_* lien*_* {c -(}
{txt}  2{com}.         rename `var' z`var'
{txt}  3{com}.         quietly replace z`var' = 0 if missing(z`var')   
{txt}  4{com}. {c )-}
{res}{txt}
{com}. replace isapt_civ = 0 if missing(isapt_civ)
{txt}(992,874 real changes made)

{com}. 
. * Zero out post civil foreclosures if occuring after Zillow transaction 
. foreach var of varlist zcivfcl*_*mos zcivfcl*_*yr {c -(}
{txt}  2{com}.         replace `var' = 0 if postcivfcldate > posttsctdate & !missing(posttsctdate)
{txt}  3{com}. {c )-}
{txt}(27 real changes made)
(9 real changes made)
(7 real changes made)
(5 real changes made)
(3 real changes made)
(7 real changes made)
(5 real changes made)
(101 real changes made)
(25 real changes made)
(20 real changes made)
(10 real changes made)
(6 real changes made)
(22 real changes made)
(15 real changes made)
(446 real changes made)
(102 real changes made)
(61 real changes made)
(45 real changes made)
(22 real changes made)
(84 real changes made)
(43 real changes made)
(1,867 real changes made)
(403 real changes made)
(213 real changes made)
(122 real changes made)
(76 real changes made)
(348 real changes made)
(155 real changes made)
(4,124 real changes made)
(853 real changes made)
(413 real changes made)
(247 real changes made)
(167 real changes made)
(759 real changes made)
(292 real changes made)

{com}. 
. * Zero out pre civil foreclosures if occuring before Zillow transaction 
. foreach var of varlist zcivfcl*_*pre {c -(}
{txt}  2{com}.         replace `var' = 0 if precivfcldate < pretsctdate & !missing(pretsctdate)
{txt}  3{com}. {c )-}
{txt}(231 real changes made)
(57 real changes made)
(37 real changes made)
(32 real changes made)
(22 real changes made)
(49 real changes made)
(24 real changes made)
(823 real changes made)
(206 real changes made)
(127 real changes made)
(110 real changes made)
(81 real changes made)
(179 real changes made)
(74 real changes made)
(2,269 real changes made)
(520 real changes made)
(304 real changes made)
(248 real changes made)
(186 real changes made)
(452 real changes made)
(168 real changes made)
(5,439 real changes made)
(1,080 real changes made)
(619 real changes made)
(531 real changes made)
(390 real changes made)
(929 real changes made)
(325 real changes made)
(9,309 real changes made)
(1,596 real changes made)
(891 real changes made)
(794 real changes made)
(585 real changes made)
(1,352 real changes made)
(441 real changes made)

{com}. 
. * Zero out liens if occuring after Zillow transaction
. foreach var of varlist zlien_*mos zlien_*yr {c -(}
{txt}  2{com}.         replace `var' = 0 if postliendate > posttsctdate& !missing(posttsctdate)
{txt}  3{com}. {c )-}
{txt}(34 real changes made)
(11 real changes made)
(3 real changes made)
(1 real change made)
(0 real changes made)
(10 real changes made)
(3 real changes made)
(171 real changes made)
(70 real changes made)
(48 real changes made)
(17 real changes made)
(10 real changes made)
(66 real changes made)
(46 real changes made)
(581 real changes made)
(233 real changes made)
(168 real changes made)
(52 real changes made)
(30 real changes made)
(226 real changes made)
(157 real changes made)
(1,591 real changes made)
(601 real changes made)
(411 real changes made)
(142 real changes made)
(84 real changes made)
(570 real changes made)
(367 real changes made)
(2,767 real changes made)
(1,063 real changes made)
(722 real changes made)
(268 real changes made)
(167 real changes made)
(985 real changes made)
(620 real changes made)

{com}. 
. * Zero out pre liens if occuring before Zillow transaction 
. foreach var of varlist zlien_*pre {c -(}
{txt}  2{com}.         replace `var' = 0 if preliendate < pretsctdate & !missing(pretsctdate)
{txt}  3{com}. {c )-}
{txt}(28 real changes made)
(13 real changes made)
(9 real changes made)
(2 real changes made)
(1 real change made)
(13 real changes made)
(9 real changes made)
(119 real changes made)
(49 real changes made)
(35 real changes made)
(17 real changes made)
(9 real changes made)
(45 real changes made)
(29 real changes made)
(401 real changes made)
(137 real changes made)
(93 real changes made)
(60 real changes made)
(35 real changes made)
(123 real changes made)
(74 real changes made)
(1,181 real changes made)
(397 real changes made)
(280 real changes made)
(172 real changes made)
(115 real changes made)
(354 real changes made)
(212 real changes made)
(2,071 real changes made)
(698 real changes made)
(505 real changes made)
(309 real changes made)
(209 real changes made)
(627 real changes made)
(376 real changes made)

{com}. 
. 
. *--------------------------------------------------
. * Combine measures
. *--------------------------------------------------
. 
. * Consolidate insolvencies/foreclosures
. foreach hor in 3yrpre 2yrpre 1yrpre 6mospre 3mospre 3mos 6mos 1yr 2yr 3yr {c -(}
{txt}  2{com}. 
.         * Consolidate foreclosures
.         gen byte zfcl_`hor' = zforeclose_`hor' // define according to Zillow post-2006
{txt}  3{com}.         replace zfcl_`hor' = zcivfcl_`hor' if year_init<=2006 // define according to civil courts pre-2006
{txt}  4{com}. 
.         * Consolidate insolvencies
.         gen byte zinsolvent_`hor' = zfcl_`hor' | zbankrupt_`hor' | zlien_`hor'  
{txt}  5{com}. {c )-}
{txt}(6,060 real changes made)
(5,101 real changes made)
(3,466 real changes made)
(2,109 real changes made)
(1,164 real changes made)
(1,314 real changes made)
(2,654 real changes made)
(5,052 real changes made)
(8,377 real changes made)
(10,494 real changes made)

{com}. 
. * Consolidate name-matched insolvencies/foreclosures
. foreach hor in 3yrpre 2yrpre 1yrpre 6mospre 3mospre 3mos 6mos 1yr 2yr 3yr {c -(}
{txt}  2{com}.         foreach type in nml nmf fml fmf dml dmf {c -(}
{txt}  3{com}. 
.                 * Consolidate foreclosures
.                 gen byte zfcl_`type'_`hor' = zforeclose_`type'_`hor' 
{txt}  4{com}.                 replace zfcl_`type'_`hor'= zcivfcl_`type'_`hor' if year_init<=2006
{txt}  5{com}. 
.                 * Consolidate insolvencies
.                 gen byte zinsolvent_`type'_`hor' = zfcl_`type'_`hor' | zbankrupt_`type'_`hor' | zlien_`type'_`hor'
{txt}  6{com}.         {c )-}
{txt}  7{com}. {c )-}
{txt}(2,222 real changes made)
(1,325 real changes made)
(1,155 real changes made)
(861 real changes made)
(1,899 real changes made)
(717 real changes made)
(1,891 real changes made)
(1,140 real changes made)
(987 real changes made)
(727 real changes made)
(1,612 real changes made)
(630 real changes made)
(1,264 real changes made)
(785 real changes made)
(649 real changes made)
(477 real changes made)
(1,081 real changes made)
(464 real changes made)
(764 real changes made)
(474 real changes made)
(393 real changes made)
(293 real changes made)
(655 real changes made)
(277 real changes made)
(417 real changes made)
(270 real changes made)
(226 real changes made)
(167 real changes made)
(353 real changes made)
(154 real changes made)
(608 real changes made)
(493 real changes made)
(588 real changes made)
(441 real changes made)
(393 real changes made)
(176 real changes made)
(1,259 real changes made)
(1,015 real changes made)
(1,221 real changes made)
(908 real changes made)
(822 real changes made)
(369 real changes made)
(2,414 real changes made)
(1,944 real changes made)
(2,354 real changes made)
(1,745 real changes made)
(1,590 real changes made)
(692 real changes made)
(3,997 real changes made)
(3,225 real changes made)
(3,888 real changes made)
(2,944 real changes made)
(2,657 real changes made)
(1,102 real changes made)
(4,978 real changes made)
(3,998 real changes made)
(4,847 real changes made)
(3,676 real changes made)
(3,307 real changes made)
(1,339 real changes made)

{com}. 
. 
. * Indicators for insolvencies with no names matched at different horizons
. foreach var in zinsolvent zfcl zbankrupt7 zlien {c -(}
{txt}  2{com}.         foreach type in l f {c -(}
{txt}  3{com}.                 foreach hor in 3yrpre 2yrpre 1yrpre 6mospre 3mospre 3mos 6mos 1yr 2yr 3yr {c -(}
{txt}  4{com}.                         gen byte `var'_nd`type'_`hor' = `var'_`hor' == 1 & `var'_nm`type'_`hor' == 0 // name does not match                     
{txt}  5{com}.                         gen byte `var'_fd`type'_`hor' = `var'_`hor' == 1 & `var'_fm`type'_`hor' == 0 // family name does not match
{txt}  6{com}.                 {c )-}       
{txt}  7{com}.         {c )-}       
{txt}  8{com}. {c )-}
{txt}
{com}. 
. *--------------------------------------------------
. * Get HPI data
. *--------------------------------------------------
. 
. * Merge
. gen zipcode = ZipCode1_
{txt}(388 missing values generated)

{com}. gen year = year_init
{txt}
{com}. merge m:1 zipcode year using data\raw\housing\zipcode_housingindex.dta, update assert(1 2 3) keep(1 3) keepusing(hpi*)
{res}{txt}{p 0 7 2}
(note: variable
zipcode was 
float, now double to accommodate using data's values)
{p_end}

{col 5}Result{col 38}# of obs.
{col 5}{hline 41}
{col 5}not matched{col 30}{res}          48,918
{txt}{col 9}from master{col 30}{res}          48,918{txt}  (_merge==1)
{col 9}from using{col 30}{res}               0{txt}  (_merge==2)

{col 5}matched{col 30}{res}       1,155,706
{txt}{col 9}not updated{col 30}{res}       1,155,706{txt}  (_merge==3)
{col 9}missing updated{col 30}{res}               0{txt}  (_merge==4)
{col 9}nonmissing conflict{col 30}{res}               0{txt}  (_merge==5)
{col 5}{hline 41}

{com}. drop _merge zipcode year
{txt}
{com}. 
. * Decreasing housing price
. local cutvar "hpichange"
{txt}
{com}. gen neg_`cutvar' = `cutvar'<0 if !missing(`cutvar')
{txt}(53,966 missing values generated)

{com}. 
. *--------------------------------------------------
. * Address validity
. *--------------------------------------------------
. 
. * Get address validity indicator
. merge 1:1 casenumber using data\raw\housing\addresses_validation.dta, assert(1 2 3) keep(1 3)
{res}
{txt}{col 5}Result{col 38}# of obs.
{col 5}{hline 41}
{col 5}not matched{col 30}{res}         100,444
{txt}{col 9}from master{col 30}{res}         100,444{txt}  (_merge==1)
{col 9}from using{col 30}{res}               0{txt}  (_merge==2)

{col 5}matched{col 30}{res}       1,104,180{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop _merge
{txt}
{com}. gen validaddress=valid=="Valid"
{txt}
{com}. gen notinvalid=valid!="Invalid"
{txt}
{com}. 
. *--------------------------------------------------
. * Organize and save
. *--------------------------------------------------
. 
. * Save
. compress
  {txt}variable {bf}detained{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_3mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_3mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_3mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_3mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_3mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_3mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_3mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_3mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_3mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_3mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_3mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_3mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_6mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_6mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_6mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_6mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_6mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_6mos{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_6mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_6mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_6mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_6mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_6mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_6mospre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_1yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_1yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_1yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_1yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_1yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_1yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_1yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_1yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_1yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_1yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_1yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_1yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_2yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_2yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_2yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_2yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_2yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_2yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_2yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_2yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_2yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_2yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_2yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_2yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_3yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_3yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_3yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_3yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_3yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_3yr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nml_3yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_nmf_3yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fml_3yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_fmf_3yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dml_3yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}zforeclose_dmf_3yrpre{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}dob{sf} was {bf}{res}float{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}date_purchase{sf} was {bf}{res}float{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}pretsctdate_high{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}preprice_high{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}preloantoprice_high{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}preloanamt_high{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}prematurity_high{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}age_purchase_high{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}neg_hpichange{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}validaddress{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}notinvalid{sf} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
{txt}  (257,789,536 bytes saved)

{com}. save analysis_sample.dta, replace
{txt}(note: file analysis_sample.dta not found)
file analysis_sample.dta saved

{com}. 
. ************************************************************************************************************
. ********************************* Aggregate data ************************************/
. ************************************************************************************************************
. 
. *--------------------------------------------------
. * Collapse criminal data by commissioner-year
. *--------------------------------------------------
. 
. * Load data
. cd "$workpath"
{res}H:\Dropbox\work\replicationpack
{txt}
{com}. use data\mdcourtsdata.dta, clear
{txt}
{com}. keep if year >=2000
{txt}(1 observation deleted)

{com}. 
. * Collapse data
. collapse (mean) init_statusROR rivtROR_comm ivROR_comm numcases_percommyr, by(year_init init_commissionerid)
{txt}
{com}. 
. * Save
. compress
  {txt}variable {bf}numcases_percommyr{sf} was {bf}{res}float{sf}{txt} now {bf}{res}int{sf}
{txt}  (10,452 bytes saved)

{com}. save mdcourtsdata_commyravg.dta, replace
{txt}(note: file mdcourtsdata_commyravg.dta not found)
file mdcourtsdata_commyravg.dta saved

{com}. 
. 
. *--------------------------------------------------
. * Collapse criminal data by year
. *--------------------------------------------------
. 
. * Load data
. cd "$workpath"
{res}H:\Dropbox\work\replicationpack
{txt}
{com}. use data\mdcourtsdata.dta, clear
{txt}
{com}. keep if year >=2000
{txt}(1 observation deleted)

{com}. 
. * Bail amount for various types
. gen final_lnbailamt = ln(final_bailamt)
{txt}(567,682 missing values generated)

{com}. foreach type in cash corp prop pct upb parb other {c -(}
{txt}  2{com}.         gen init_bailamt_`type' = init_bailamt if bond_`type' == 1
{txt}  3{com}.         gen init_lnbailamt_`type' = init_lnbailamt if bond_`type' == 1
{txt}  4{com}.         gen final_bailamt_`type' = final_bailamt if bond_`type'==1
{txt}  5{com}.         gen final_lnbailamt_`type' = ln(final_bailamt) if bond_`type'==1
{txt}  6{com}. {c )-}
{txt}(1,183,247 missing values generated)
(1,193,015 missing values generated)
(1,183,247 missing values generated)
(1,188,039 missing values generated)
(1,021,002 missing values generated)
(1,046,494 missing values generated)
(1,021,002 missing values generated)
(1,031,015 missing values generated)
(1,168,404 missing values generated)
(1,173,016 missing values generated)
(1,168,404 missing values generated)
(1,170,615 missing values generated)
(1,187,547 missing values generated)
(1,191,480 missing values generated)
(1,187,547 missing values generated)
(1,189,483 missing values generated)
(1,174,921 missing values generated)
(1,184,606 missing values generated)
(1,174,921 missing values generated)
(1,178,672 missing values generated)
(1,200,970 missing values generated)
(1,202,089 missing values generated)
(1,200,970 missing values generated)
(1,201,805 missing values generated)
(1,154,190 missing values generated)
(1,168,927 missing values generated)
(1,154,190 missing values generated)
(1,160,712 missing values generated)

{com}. 
. * Collapse
. collapse (mean) *iv*ROR_comm bond_* wari_* disposition_guilty init_lnbailamt* init_bailamt* final_lnbailamt* final_bailamt* init_statusROR init_statusHDOB init_statusHWOB timelyrelease*, by(year_init)
{txt}
{com}. 
. * Save
. isid year_init
{txt}
{com}. compress
{txt}  (0 bytes saved)

{com}. save mdcourtsdata_annualavg.dta, replace
{txt}(note: file mdcourtsdata_annualavg.dta not found)
file mdcourtsdata_annualavg.dta saved

{com}. 
. *--------------------------------------------------
. * Collapse civil courts data by year
. *--------------------------------------------------
. 
. * Load data
. cd "$workpath"
{res}H:\Dropbox\work\replicationpack
{txt}
{com}. use data\civilcases_address.dta, clear
{txt}
{com}. gen year = year(case_date)
{txt}(255 missing values generated)

{com}. keep if inrange(year,2000,2020)
{txt}(143,485 observations deleted)

{com}. 
. * Collapse
. collapse (sum) foreclosure lien, by(year)
{txt}
{com}. 
. * Save
. compress
  {txt}variable {bf}year{sf} was {bf}{res}float{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}foreclosure{sf} was {bf}{res}double{sf}{txt} now {bf}{res}long{sf}
  {txt}variable {bf}lien{sf} was {bf}{res}double{sf}{txt} now {bf}{res}long{sf}
{txt}  (210 bytes saved)

{com}. save mdcivildata_annualavg.dta, replace
{txt}(note: file mdcivildata_annualavg.dta not found)
file mdcivildata_annualavg.dta saved

{com}. 
. *--------------------------------------------------
. * Collapse Zillow data by year
. *--------------------------------------------------
. 
. * Get transactions
. cd "$workpath"
{res}H:\Dropbox\work\replicationpack
{txt}
{com}. use data\transactions_cleaned.dta, clear
{txt}
{com}. 
. * Foreclosures and sales
. gen sale = !foreclosed
{txt}
{com}. 
. * Collapse
. collapse (sum) sale foreclosed, by(year)
{txt}
{com}. 
. * Save
. compress
  {txt}variable {bf}sale{sf} was {bf}{res}double{sf}{txt} now {bf}{res}long{sf}
  {txt}variable {bf}foreclosed{sf} was {bf}{res}double{sf}{txt} now {bf}{res}long{sf}
{txt}  (232 bytes saved)

{com}. save zillow_annavg.dta, replace
{txt}(note: file zillow_annavg.dta not found)
file zillow_annavg.dta saved

{com}. 
. *--------------------------------------------------
. * Collapse bankruptcy data by year
. *--------------------------------------------------
. 
. * Load data
. cd "$workpath"
{res}H:\Dropbox\work\replicationpack
{txt}
{com}. use data\gnw_bankruptcy_addresses.dta, clear
{txt}
{com}. 
. rename fyear year
{res}{txt}
{com}. keep if inrange(year,2000,2020)
{txt}(244,203 observations deleted)

{com}. 
. * Chapter 7/13 dummies
. gen bankrupt_ch7 = chapter == 7
{txt}
{com}. gen bankrupt_ch13 = chapter == 13
{txt}
{com}. 
. * Collapse
. collapse (sum) bankrupt_ch* (count) bankrupt = chapter , by(year)
{txt}
{com}. 
. * Save
. compress
  {txt}variable {bf}bankrupt_ch7{sf} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}bankrupt_ch13{sf} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
{txt}  (144 bytes saved)

{com}. save bankruptcies_annualavg.dta, replace
{txt}(note: file bankruptcies_annualavg.dta not found)
file bankruptcies_annualavg.dta saved

{com}. 
. **************************************************************************************************************
. 
. * Close log
. log close
      {txt}name:  {res}<unnamed>
       {txt}log:  {res}H:\Dropbox\work\replicationpack\logs\2_PrepSample.smcl
  {txt}log type:  {res}smcl
 {txt}closed on:  {res}29 Nov 2024, 21:04:20
{txt}{.-}
{smcl}
{txt}{sf}{ul off}